package com.navi.loaddata.loadService.impl.Dcdata;

import com.navi.loaddata.core.errorLog.ErrorCode;
import com.navi.loaddata.core.etlException.ETLException2;
import com.navi.loaddata.core.mesEntity.*;
import com.navi.loaddata.core.mysqlEntity.EdaPdsConfDModel;
import com.navi.loaddata.core.mysqlEntity.EtlConfD;
import com.navi.loaddata.core.mysqlEntity.EtlConfDModel;
import com.navi.loaddata.core.repository.impl.MesRepositoryImpl;
import com.navi.loaddata.core.repository.impl.MySqlRepositoryImpl;
import com.navi.loaddata.core.util.JdbcUtil;
import com.navi.loaddata.core.util.LoggerUtil;
import com.navi.loaddata.core.util.ProcessUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

@Service
@Transactional(transactionManager = "mesPlatformTransactionManager")
public class LoadDcInfoService {

    @Autowired
    private MesRepositoryImpl mesdao;

    public List<SpcDcdata> loadDcDataList(EtlConfDModel etlConfD, String dctype, List<String> unitnameList) throws Exception {
        String factoryName;
        if ("OC".equals(etlConfD.getShop())) {
            factoryName = "OC";
        } else {
            factoryName = "ACF";
        }
        List<SpcDcdata> list1 = new ArrayList<>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        StringBuffer hql = new StringBuffer();
        hql.append("FROM SpcDcdata WHERE 1=1")
                .append(" and factoryname='" + factoryName + "'")
                .append(ProcessUtil.getShopMesSpcDataHql(etlConfD.getShop()))//index
                .append(" and updatetime >=to_date('" + sdf.format(etlConfD.getRunStartTimestamp()) + "','YYYYMMDDHH24MISS')")//index
                .append(" and updatetime < to_date('" + sdf.format(etlConfD.getRunEndTimestamp()) + "','YYYYMMDDHH24MISS')")
                .append(ProcessUtil.getOpeMesSpcDataHql(etlConfD.getOpe()))
                .append(ProcessUtil.getEqptMesSpcDataHql(etlConfD.getEqpt()))//index
                .append(" and dctype IN(").append(dctype).append(")")
                .append(" and instr(processoperationname,'DUM') = 0 AND nvl(SUBSTR(processoperationname, 6,7),'X') <> 'D'");
        //子机台
        hql.append("and (");
        unitnameList.forEach(subeq -> hql.append(" unitname like'" + StringUtils.replace(subeq, "*", "%") + "' or"));
        hql.deleteCharAt(hql.length() - 1);
        hql.deleteCharAt(hql.length() - 1);
        hql.append(")");
        try {
            list1 = mesdao.list(SpcDcdata.class, hql.toString());
        } catch (Exception e) {
            throw new ETLException2(ErrorCode.ERROR_GET_MES_HIS_SPCDC, e);
        }
        return list1;
    }

    public List<SpcDcResultData> loadMDataList(EtlConfDModel etlConfD, long dcdataid, List<String> itemnames) {
        List<SpcDcmeasuredataresult> spcDcmeasuredataresultList = new ArrayList<>();
        List<SpcDcResultData> spcDcResultDataList = new ArrayList<>();
        StringBuffer hql = new StringBuffer();
        hql.append("FROM SpcDcmeasuredataresult WHERE 1=1")
                .append(" and dcdataid =" + dcdataid);
        hql.append(" and itemname in(");
        itemnames.forEach(itemname -> hql.append("'" + itemname + "',"));
        hql.deleteCharAt(hql.length() - 1);
        hql.append(")");
        try {

            spcDcmeasuredataresultList = mesdao.list(SpcDcmeasuredataresult.class, hql.toString());
            spcDcmeasuredataresultList.forEach(e -> {
                SpcDcResultData spcDcResultData = new SpcDcResultData();
                spcDcResultData.setDcdataid(e.getDcdataid());
                spcDcResultData.setSeq(e.getSeq());
                spcDcResultData.setSamplematerialname(ProcessUtil.processString(e.getSamplematerialname()));
                spcDcResultData.setItemname(ProcessUtil.processString(e.getItemname()));
                spcDcResultData.setSitename(ProcessUtil.processString(e.getSitename()));
                spcDcResultData.setSitetype(ProcessUtil.processString(e.getSitetype()));
                spcDcResultData.setAliasitemname(ProcessUtil.processString(e.getAliasitemname()));
                spcDcResultData.setDatatype(ProcessUtil.processString(e.getDatatype()));
                spcDcResultData.setResult(ProcessUtil.processString(e.getResult()));
                spcDcResultData.setEventname(ProcessUtil.processString(e.getEventname()));
                spcDcResultData.setTimekey(ProcessUtil.processString(e.getTimekey()));
                spcDcResultData.setEventtime(e.getEventtime());
                spcDcResultData.setEventuser(ProcessUtil.processString(e.getEventuser()));
                spcDcResultData.setEventcomment(ProcessUtil.processString(e.getEventcomment()));
                spcDcResultData.setUpdatetime(e.getUpdatetime());
                spcDcResultDataList.add(spcDcResultData);
            });
        } catch (Exception e) {
            throw new ETLException2(ErrorCode.ERROR_GET_MES_HIS_SPC_RESULT_DC_DCTYPE_I, e);
        }
        return spcDcResultDataList;
    }

    public List<SpcDcResultData> loadPDataList(EtlConfDModel etlConfD, long dcdataid, List<String> itemnames) {
        List<SpcDcprocessdataresult> spcDcprocessdataresult = new ArrayList<>();
        List<SpcDcResultData> spcDcResultDataList = new ArrayList<>();
        StringBuffer hql = new StringBuffer();
        hql.append("FROM SpcDcprocessdataresult WHERE 1=1")
                .append(" and dcdataid =" + dcdataid);
        hql.append("and itemname in(");
        itemnames.forEach(itemname -> hql.append("'" + itemname + "',"));
        hql.deleteCharAt(hql.length() - 1);
        hql.append(")");
        try {
            spcDcprocessdataresult = mesdao.list(SpcDcprocessdataresult.class, hql.toString());
            spcDcprocessdataresult.forEach(e -> {
                SpcDcResultData spcDcResultData = new SpcDcResultData();
                spcDcResultData.setDcdataid(e.getDcdataid());
                spcDcResultData.setSeq(e.getSeq());
                spcDcResultData.setSamplematerialname(ProcessUtil.processString(e.getSamplematerialname()));
                spcDcResultData.setItemname(ProcessUtil.processString(e.getItemname()));
                spcDcResultData.setSitename(ProcessUtil.processString(e.getSitename()));
                spcDcResultData.setSitetype(ProcessUtil.processString(e.getSitetype()));
                spcDcResultData.setAliasitemname(ProcessUtil.processString(e.getAliasitemname()));
                spcDcResultData.setDatatype(ProcessUtil.processString(e.getDatatype()));
                spcDcResultData.setResult(ProcessUtil.processString(e.getResult()));
                spcDcResultData.setEventname(ProcessUtil.processString(e.getEventname()));
                spcDcResultData.setTimekey(ProcessUtil.processString(e.getTimekey()));
                spcDcResultData.setEventtime(e.getEventtime());
                spcDcResultData.setEventuser(ProcessUtil.processString(e.getEventuser()));
                spcDcResultData.setEventcomment(ProcessUtil.processString(e.getEventcomment()));
                spcDcResultData.setUpdatetime(e.getUpdatetime());
                spcDcResultDataList.add(spcDcResultData);
            });
        } catch (Exception e) {
            throw new ETLException2(ErrorCode.ERROR_GET_MES_HIS_SPC_RESULT_DC_DCTYPE_P, e);
        }
        return spcDcResultDataList;
    }

    public List<String> getUnitNameList(EtlConfDModel etlConfD) {
        List<String> unitName = new ArrayList<>();
        List<EdaPdsConfDModel> edaPdsConf = new ArrayList<>();
        String sql = "select distinct subeqpt from eda_pds_conf_d WHERE VALID_FLG='Y'" +
                " AND SHOP='" + etlConfD.getShop() + "'";
        try {
            QueryRunner qr_mysql = JdbcUtil.getQueryRunner_mysql();
            edaPdsConf = qr_mysql.query(sql, new BeanListHandler<EdaPdsConfDModel>(EdaPdsConfDModel.class));
        } catch (Exception e) {
            throw new ETLException2(ErrorCode.ERROR_GET_MYSQL_PDS_CONF_D, e);
        }
        if (edaPdsConf.isEmpty()) throw new ETLException2(ErrorCode.ERROR_GET_MYSQL_PDS_CONF_D, null);
        edaPdsConf.forEach(e -> unitName.add(e.getSubeqpt()));
        return unitName;
    }


    public HashMap<String, List<String>> getItemNames(EtlConfDModel etlConfD) {
        HashMap<String, List<String>> itemNames = new HashMap<>();
        String sql = "select SUBEQPT,ITEMNAME from eda_pds_conf_d WHERE VALID_FLG='Y'" +
                " AND SHOP='" + etlConfD.getShop() + "'" +
                " AND EQPT='" + etlConfD.getEqpt().substring(0, 4) + "*" + "'";
        try {
            QueryRunner qr_mysql = JdbcUtil.getQueryRunner_mysql();
            List<EdaPdsConfDModel> edaPdsConf = qr_mysql.query(sql, new BeanListHandler<EdaPdsConfDModel>(EdaPdsConfDModel.class));

            if (!edaPdsConf.isEmpty()) {
                edaPdsConf.forEach(item -> {
                    if (null == itemNames.get(item.getSubeqpt())) {
                        List<String> list = new ArrayList<>();
                        list.add(item.getItemname());
                        itemNames.put(item.getSubeqpt(), list);
                    } else {
                        itemNames.get(item.getSubeqpt()).add(item.getItemname());
                    }
                });
            }
        } catch (Exception e) {
            throw new ETLException2(ErrorCode.ERROR_GET_MYSQL_PDS_CONF_D, e);
        }
        return itemNames;
    }


    public HashMap<String, SpcChartMShopEqpt> getSpcChartMMap(HashMap<String, List<String>> subeqItemName) {
        //ITEMNAME,SpcChartMShopEqpt
        HashMap<String, SpcChartMShopEqpt> spcChartMMap = new HashMap<>();
        StringBuffer sqlEqptItem=new StringBuffer();
        for(String subEqpt:subeqItemName.keySet()){
            sqlEqptItem.append(" ("+getUnitEqptIdHql(subEqpt)+" and  CD.ALIASITEMNAME in(");
            subeqItemName.get(subEqpt).forEach(itemName->sqlEqptItem.append("'" + itemName + "',"));
            sqlEqptItem.deleteCharAt(sqlEqptItem.length()-1);
            sqlEqptItem.append(")) OR");
        }
        sqlEqptItem.deleteCharAt(sqlEqptItem.length()-1).deleteCharAt(sqlEqptItem.length()-1);
        try {
            QueryRunner qr_mes = JdbcUtil.getQueryRunner_mes();
            StringBuffer sql1 = new StringBuffer();
            sql1.append("select CD.ALIASITEMNAME AS itemName, CS.TARGET AS target, CS.UPPERSPECLIMIT AS usl, CS.LOWERSPECLIMIT AS lsl")
                    .append(" from  SPC_CHARTDEF CD , spc_chartspec CS")
                    .append(" where 1=1")
                    .append(" and CS.CHARTID=CD.CHARTID")
                    .append(" and CHARTNAME in ('I','XBar','P','NP','U')")
                    .append(" AND ("+sqlEqptItem+")");


            StringBuffer sql2 = new StringBuffer();

            sql2.append("select CD.ALIASITEMNAME, CCS.CENTERLINE cl, CCS.UPPERCONTROLLIMIT AS ucl, CCS.LOWERCONTROLLIMIT AS lcl" )
                    .append(" from SPC_CHARTDEF CD ,  spc_chartcontrolspec CCS")
                    .append(" where 1=1")
                    .append(" and CCS.CHARTID=CD.CHARTID")
                    .append(" and CHARTNAME in ('I','XBar','P','NP','U')")
                    .append(" AND ("+sqlEqptItem+")");

            List<SpcChartMShopEqpt> spcChartMListS = qr_mes.query(sql1.toString(), new BeanListHandler<SpcChartMShopEqpt>(SpcChartMShopEqpt.class));
            List<SpcChartMShopEqpt> spcChartMListC = qr_mes.query(sql2.toString(), new BeanListHandler<SpcChartMShopEqpt>(SpcChartMShopEqpt.class));

            if (!spcChartMListS.isEmpty()) spcChartMListS.forEach(e->spcChartMMap.put(e.getItemName(),e));
            if(!spcChartMListC.isEmpty()){
                for(SpcChartMShopEqpt e:spcChartMListC){
                    if(null==spcChartMMap.get(e.getItemName())){
                        spcChartMMap.put(e.getItemName(),e);
                    }else{
                        spcChartMMap.get(e.getItemName()).setCl(e.getCl());
                        spcChartMMap.get(e.getItemName()).setUcl(e.getUcl());
                        spcChartMMap.get(e.getItemName()).setLcl(e.getLcl());
                    }
                }
            }
        } catch (Exception e) {
            throw new ETLException2(ErrorCode.ERROR_GET_MES_SPC_CHART_M, e);
        }
        return spcChartMMap;
    }

    private String getUnitEqptIdHql(String confEqpt) {
        if(StringUtils.isEmpty(confEqpt))return "";
        if(confEqpt.contains("*")) return " unitname like'"+StringUtils.replace(confEqpt,"*","%")+"' ";
        return " unitname ='"+confEqpt+"' ";
    }
}
